package com.spoto.oa.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import com.spoto.oa.dao.NoticeDAO;
import com.spoto.oa.model.Notice;
import com.spoto.oa.util.DB;

/**
 * 
 * @Project: OA
 * @Description:公告notice的DAO的具体实现 
 * @Author: LinYiSong
 * @Vesion:
 * @Date: 2010-10-16~2010-10-16
 */
public class NoticeDAOImpl implements NoticeDAO {

	/**
	 * 保存Notice
	 * @param notice
	 * @throws SQLException 
	 */
	public void save(Notice notice) throws SQLException {
		String title = notice.getTitle();
		String noticeType = notice.getType();
		String content = notice.getContent();
		String username = notice.getCreator();
		java.sql.Date create_date = notice.getCreate_date();

		PreparedStatement pstm = DB
				.getConn()
				.prepareStatement(
						"insert into notice(title,type,content,creator,create_date) "
								+ "values(?,?,?,?,?)");
		pstm.setString(1,title);
		pstm.setString(2, noticeType);
		pstm.setString(3, content);
		pstm.setString(4, username);
		pstm.setDate(5,create_date);

		pstm.executeUpdate();
	}
	/**
	 * 取得所有公告
	 * @return
	 */
	public List<Notice> getAllNotices() {
		
		PreparedStatement pstm;
		ResultSet rs = null;
		try {
			pstm = DB.getConn().prepareStatement("select id,title,create_date,creator,type,content from notice");
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}
	/**
	 * 将ResultSet封装成list
	 * 
	 * @param rs
	 * @return
	 */
	private List<Notice> resultSetToList(ResultSet rs) {
		List<Notice> notices = new ArrayList<Notice>();

		try {
			while (rs.next()) {
				Notice notice = new Notice();
				notice.setId(rs.getInt("id"));
				notice.setTitle(rs.getString("title"));
				notice.setCreate_date(rs.getDate("create_date"));
				notice.setCreator(rs.getString("creator"));
				notice.setContent(rs.getString("content"));
				notice.setType(rs.getString("type"));
				notices.add(notice);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return notices;
	}
	/**
	 * 根据id删除公告
	 * @param id
	 * @throws SQLException 
	 */
	public void deleteById(int id) throws SQLException {
		PreparedStatement pstm;
		pstm = DB.getConn().prepareStatement("delete from notice where id=?");
		pstm.setInt(1, id);
		pstm.executeUpdate();
	}
	
	/**
	 * 根据id取得对应的公告
	 * @throws SQLException 
	 */
	public Notice getById(int id) throws SQLException {
		ResultSet rs = null;
		PreparedStatement pstm;
		pstm = DB.getConn().prepareStatement("select id,title,create_date,creator,type,content from notice  where id=?");
		pstm.setInt(1, id);
		rs=pstm.executeQuery();
		return this.resultSetToList(rs).get(0);
	}
	
	/**
	 * 更新
	 * @throws SQLException 
	 */
	public void update(Notice notice) {
		PreparedStatement pstm;
		try {
			pstm = DB.getConn().prepareStatement("update notice set title=?,create_date=?,creator=?,type=?,content=?  where id=?");
			pstm.setString(1, notice.getTitle());
			pstm.setDate(2, notice.getCreate_date());
			pstm.setString(3, notice.getCreator());
			pstm.setString(4, notice.getType());
			pstm.setString(5, notice.getContent());
			pstm.setInt(6, notice.getId());
			
			pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	/**
	 * 取得某种公告类型的所有公告信息
	 * @param searchType
	 * @return
	 */
	public List<Notice> getAllByType(String searchType) {
		PreparedStatement pstm;
		ResultSet rs = null;
		try {
			pstm = DB.getConn().prepareStatement("select id,title,create_date,creator,type,content from notice where type=?");
			pstm.setString(1, searchType);
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}
	/**
	 * 取得某种公告类型的所有公告信息
	 * @param type
	 * @param title
	 * @return
	 */
	public List<Notice> getAllByTypeAndTitle(String type, String title) {
		PreparedStatement pstm;
		ResultSet rs = null;
		try {
			pstm = DB.getConn().prepareStatement("select id,title,create_date,creator,type,content from notice where type=? and title like ?");
			pstm.setString(1, type);
			title="%"+title+"%";
			pstm.setString(2, title);
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

}
